2.5. Exploratory data analysis

raw_beds <- read_csv("data/beds_by_nhs_board_of_treatment_and_specialty.csv")

raw_activity <- read_csv("data/inpatient_and_daycase_by_nhs_board_of_treatment_and_specialty.csv")

Cleaning Data

wranged_beds_no_gj <- raw_beds %>% 
  clean_names() %>% 
  mutate(q = str_sub(quarter, -2)) %>% 
  # Change to date format
  mutate(quarter = yq(quarter)) %>% 
  # rename the columns above 
  mutate(date = quarter,
         quarter = q) %>% 
  # Change Heath Board names to read better
  mutate(hb = if_else(hb %in% "S08000015", "Ayrshire & Arran",
                      if_else(hb %in% "S08000016", "Borders",
                      if_else(hb %in% "S08000017", "Dumfries & Galloway",
                      if_else(hb %in% "S08000019", "Forth Valley",
                      if_else(hb %in% "S08000020", "Grampian",
                      if_else(hb %in% "S08000022", "Highland",
                      if_else(hb %in% "S08000024", "Lothian",
                      if_else(hb %in% "S08000025", "Orkney",
                      if_else(hb %in% "S08000026", "Shetland",
                      if_else(hb %in% "S08000028", "Western Isles",
                      if_else(hb %in% "S08000029", "Fife",
                      if_else(hb %in% "S08000030", "Tayside",
                      if_else(hb %in% "S08000031", "Greater Glasgow & Clyde",
                      if_else(hb %in% "S08000032", "Lanarkshire",
                      if_else(hb %in% "S92000003", "Scotland", NA_character_)
                      ))))))))))))))) %>% 
  # take out location names to take out duplicate
  filter(location_qf == "d") %>%
  
  # Take out of cleaning script and add when pulling in shiny
  
  # take out Golden Jubilee
  filter(hb != is.na(hb)) %>%
  # take out unused columns
  # take out location to remove duplicate
  select(date, quarter, hb, specialty_name, all_staffed_beds, 
         total_occupied_beds, average_available_staffed_beds,
         average_occupied_beds, percentage_occupancy)
  
wranged_beds_no_gj

activity_no_gj <- raw_activity %>% 
  clean_names() %>% 
  # Create new column for quarters
  mutate(q = str_sub(quarter, -2)) %>% 
  # Change to date format
  mutate(quarter = yq(quarter)) %>% 
  # rename the columns above 
  mutate(date = quarter,
         quarter = q) %>% 
  # Change Heath Board names to read better
  mutate(
    hb = if_else(hb %in% "S08000015", "Ayrshire & Arran",
                  if_else(hb %in% "S08000016", "Borders",
                  if_else(hb %in% "S08000017", "Dumfries & Galloway",
                  if_else(hb %in% "S08000019", "Forth Valley",
                  if_else(hb %in% "S08000020", "Grampian",
                  if_else(hb %in% "S08000022", "Highland",
                  if_else(hb %in% "S08000024", "Lothian",
                  if_else(hb %in% "S08000025", "Orkney",
                  if_else(hb %in% "S08000026", "Shetland",
                  if_else(hb %in% "S08000028", "Western Isles",
                  if_else(hb %in% "S08000029", "Fife",
                  if_else(hb %in% "S08000030", "Tayside",
                  if_else(hb %in% "S08000031", "Greater Glasgow & Clyde",
                  if_else(hb %in% "S08000032", "Lanarkshire",
                  if_else(hb %in% "S92000003", "Scotland", NA_character_)
                  ))))))))))))))) %>% 
  # take out location names to take out duplicate
  filter(location_qf == "d") %>% 
  
  # Take out of cleaning script and add when pulling in shiny
  
  
  # take out Golden Jubilee
  filter(hb != is.na(hb)) %>%
  # take out unused columns
  select(date, quarter, hb, admission_type, 
         specialty, specialty_name, episodes, 
         length_of_episode, average_length_of_episode, 
         spells, length_of_spell, average_length_of_spell)
  
activity_no_gj 

Exploratory analysis

unique(activity_no_gj$specialty_name)

##  [1] "General Medicine"               "Cardiology"                    
##  [3] "Infectious Diseases"            "Dermatology"                   
##  [5] "Endocrinology & Diabetes"       "Gastroenterology"              
##  [7] "Geriatric Medicine"             "Medical Oncology"              
##  [9] "Renal Medicine"                 "Rehabilitation Medicine"       
## [11] "Respiratory Medicine"           "Rheumatology"                  
## [13] "General Surgery"                "Anaesthetics"                  
## [15] "Ear, Nose & Throat (ENT)"       "Ophthalmology"                 
## [17] "Trauma and Orthopaedic Surgery" "Plastic Surgery"               
## [19] "Urology"                        "Haematology"                   
## [21] "Neurology"                      "Palliative Medicine"           
## [23] "Clinical Oncology"              "Clinical Genetics"             
## [25] "Cardiothoracic Surgery"         "Neurosurgery"                  
## [27] "Allergy"                        "Intensive Care Medicine"       
## [29] "Clinical Radiology"             "Immunology"                    
## [31] "Clinical Neurophysiology"       "Genito-Urinary Medicine"

unique(wranged_beds_no_gj$hb)

##  [1] "Ayrshire & Arran"        "Borders"                
##  [3] "Dumfries & Galloway"     "Fife"                   
##  [5] "Forth Valley"            "Grampian"               
##  [7] "Greater Glasgow & Clyde" "Highland"               
##  [9] "Lanarkshire"             "Lothian"                
## [11] "Orkney"                  "Shetland"               
## [13] "Tayside"                 "Western Isles"          
## [15] "Scotland"

KPI: Capacity – what is happening to the number of beds over the period? Perhaps think about the specialities these are if there has been specific variation?

Shows the percentage occupancy per Specialty grouping

wranged_beds_no_gj %>% 
  filter(str_detect(specialty_name, " Grouping"),
         specialty_name != "Other Grouping",
         hb == "Scotland") %>% 
  mutate(specialty_name = str_sub(specialty_name, start = 1, end = -9)) %>%
  ggplot(aes(x = date, y = percentage_occupancy, col = specialty_name)) +
  geom_point() +
  geom_line()

Obstetrics and Dental look odd as they have 100% occupancy some of the time, some show seasonal peaks.

Can change the head() in the chunk below to filter down the specialty. For shiny can filter by the highest occupancy or the specialty_name Have removed Obstetrics and Dental just to make clearer

bigest_occupancy <- wranged_beds_no_gj %>% 
  filter(str_detect(specialty_name, " Grouping"),
         specialty_name != "Other Grouping",
         specialty_name != "Obstetrics Grouping",
         specialty_name != "Dental Grouping",
         hb == "Scotland") %>% 
  group_by(specialty_name) %>% 
  summarise(count = sum(percentage_occupancy)) %>% 
  arrange(desc(count)) %>% 
  # change head to 
  head(5)

wranged_beds_no_gj %>% 
  filter(specialty_name %in% c(bigest_occupancy$specialty_name)) %>%
  filter(str_detect(specialty_name, " Grouping"),
         specialty_name != "Other Grouping",
         hb == "Scotland") %>% 
  mutate(specialty_name = str_sub(specialty_name, start = 1, end = -9)) %>%
  ggplot(aes(x = date, y = percentage_occupancy, col = specialty_name)) +
  geom_point() +
  geom_line()

This shows the regions

Can use for shiny changing the specialty_name.

Can add try to add a label to show what the highest number is

shows remote areas don’t need/uses some services so have belive have less can over lay the number total_occupied_beds at high point to give scale

wranged_beds_no_gj %>% 
  filter(str_detect(specialty_name, " Grouping"),
         specialty_name != "Other Grouping",
         specialty_name == "Emergency Grouping") %>% 
  mutate(specialty_name = str_sub(specialty_name, start = 1, end = -9)) %>%
  ggplot(aes(x = date, y = percentage_occupancy, col = hb)) +
  geom_point() +
  geom_line() +
  facet_wrap(~ hb) +
  theme(legend.position="none")

seasonal trends for staffed beds in the top 3 specialty

Have taken the top 3 to show for ease. For shiny can filter by the highest occupancy or the specialty_name

bigest_specialty <- wranged_beds_no_gj %>% 
  filter(str_detect(specialty_name, " Grouping"),
         specialty_name != "Other Grouping",
         hb == "Scotland") %>% 
  group_by(specialty_name) %>% 
  summarise(count = sum(all_staffed_beds)) %>% 
  arrange(desc(count)) %>% 
  head(3)


wranged_beds_no_gj %>% 
  filter(specialty_name %in% c(bigest_specialty$specialty_name)) %>% 
  filter(str_detect(specialty_name, " Grouping"),
         specialty_name != "Other Grouping",
         hb == "Scotland") %>% 
  mutate(specialty_name = str_sub(specialty_name, start = 1, end = -9)) %>%
  ggplot(aes(x = date, y = all_staffed_beds, col = specialty_name)) +
  geom_point() +
  geom_line()

These two are more general shorts of the bed occupancy over Scotland. Can do a tab for both of them togeter

Shows the seasonal peaks in occupancy in Q1 & Q4 Also show a steady decrease in the number of staffed beds from 2016 to 2019

wranged_beds_no_gj %>% 
  filter(specialty_name %in% c("All Acute", "All Specialties"),
         hb %in% "Scotland") %>% 
  ggplot(aes(x = date, y = percentage_occupancy, col = specialty_name)) +
  geom_point() +
  geom_line()

wranged_beds_no_gj %>% 
  filter(specialty_name %in% c("All Acute", "All Specialties"),
         hb %in% "Scotland") %>%  
  ggplot(aes(x = date, y = average_occupied_beds, col = specialty_name)) +
  # geom_col(position = "dodge")
  geom_point() +
  geom_line()

Shows the regions brake down

Glasgow is the hub for most types of beds

wranged_beds_no_gj %>% 
  filter(specialty_name %in% c("All Acute", "All Specialties"),
         hb != "Scotland") %>%
  ggplot(aes(x = date, y = all_staffed_beds, col = specialty_name)) +
  geom_point() +
  geom_line() +
  facet_wrap(~ hb) +
  theme(legend.position="none")

wranged_beds_no_gj %>% 
  filter(specialty_name %in% c("All Acute", "All Specialties"),
         hb != "Scotland") %>%
  ggplot(aes(x = date, y = percentage_occupancy, col = specialty_name)) +
  geom_point() +
  geom_line() +
  facet_wrap(~ hb) +
  theme(legend.position="none")

Can use this in shiny just filter it down by specialty

Can have both elective or emergency admissions show between the two graphs

activity_no_gj %>%  
  filter(hb %in% "Scotland",
         admission_type %in% "Emergency Inpatients") %>% 
  ggplot(aes(x = date, y = episodes, col = specialty_name)) +
  geom_point() +
  geom_line()

activity_no_gj %>%  
  filter(hb %in% "Scotland",
         admission_type %in% "Emergency Inpatients",
         specialty_name %in% "General Medicine") %>% 
 # group_by(episodes) %>% 
  ggplot(aes(x = date, y = episodes, col = specialty_name)) +
  geom_point() +
  geom_line()

activity_no_gj %>%  
  filter(hb %in% "Scotland",
         admission_type %in% "Elective Inpatients") %>%
  ggplot(aes(x = date, y = episodes, col = specialty_name)) +
  geom_point() +
  geom_line()

Admission by specialty

Better can shiny this one and pick the specialty_name

KPI: Do people have longer lengths of stay from elective or emergency admissions?

KPI: The whole system: from what’s coming through the front door in A&E and then how it filters through; how many emergency admissions are there, how many electives, how does this impact the lengths of stay people have in hospital?

take out “All Inpatients and Day cases” & “Not Specified” as duplicating and not really any there

Episodes by Admission

activity_no_gj %>%  
  filter(hb %in% "Scotland",
         specialty_name %in% "Infectious Diseases",
         admission_type %in% c("Elective Inpatients", 
                               "Emergency Inpatients", 
                               "Transfers")) %>% 
  ggplot(aes(x = date, y = episodes, col = admission_type)) +
  geom_point() +
  geom_line()

##### Spells by Admission

activity_no_gj %>%  
  filter(hb %in% "Scotland",
         specialty_name %in% "Infectious Diseases",
         admission_type %in% c("Elective Inpatients", 
                               "Emergency Inpatients", 
                               "Transfers")) %>% 
  ggplot(aes(x = date, y = spells, col = admission_type)) +
  geom_point() +
  geom_line()

Can show by regions brake down

Tayside had a larger bump in Emergency Inpatients durung the pandemic “Infectious Diseases”

activity_no_gj %>%  
  filter(hb != "Scotland",
         specialty_name %in% "Infectious Diseases",
         admission_type %in% c("Elective Inpatients", 
                               "Emergency Inpatients", 
                               "Transfers")) %>% 
  ggplot(aes(x = date, y = spells, col = admission_type)) +
  geom_point() +
  geom_line() +
  facet_wrap(~ hb) +
  theme(legend.position="none")

activity_no_gj %>%  
  filter(hb != "Scotland",
         specialty_name %in% "General Surgery",
         admission_type %in% c("Elective Inpatients", 
                               "Emergency Inpatients", 
                               "Transfers")) %>% 
  ggplot(aes(x = date, y = episodes, col = admission_type)) +
  geom_point() +
  geom_line() +
  facet_wrap(~ hb) +
  theme(legend.position="none")

“Endocrinology & Diabetes” “Emergency Inpatients” fife has a large ingress then decrees Lothian is very seasonal summer

“Medical Oncology Much be three areas of treatment Greater Glasgow & Clyde similar Elective Inpatients, Emergency Inpatients Grampian Lothian high amount

unique(activity_no_gj$admission_type)

## [1] "Elective Inpatients"          "Emergency Inpatients"        
## [3] "Transfers"                    "All Day cases"               
## [5] "All Inpatients"               "All Inpatients and Day cases"
## [7] "Not Specified"

unique(activity_no_gj$specialty_name)

##  [1] "General Medicine"               "Cardiology"                    
##  [3] "Infectious Diseases"            "Dermatology"                   
##  [5] "Endocrinology & Diabetes"       "Gastroenterology"              
##  [7] "Geriatric Medicine"             "Medical Oncology"              
##  [9] "Renal Medicine"                 "Rehabilitation Medicine"       
## [11] "Respiratory Medicine"           "Rheumatology"                  
## [13] "General Surgery"                "Anaesthetics"                  
## [15] "Ear, Nose & Throat (ENT)"       "Ophthalmology"                 
## [17] "Trauma and Orthopaedic Surgery" "Plastic Surgery"               
## [19] "Urology"                        "Haematology"                   
## [21] "Neurology"                      "Palliative Medicine"           
## [23] "Clinical Oncology"              "Clinical Genetics"             
## [25] "Cardiothoracic Surgery"         "Neurosurgery"                  
## [27] "Allergy"                        "Intensive Care Medicine"       
## [29] "Clinical Radiology"             "Immunology"                    
## [31] "Clinical Neurophysiology"       "Genito-Urinary Medicine"

unique(activity_no_gj$hb)

##  [1] "Ayrshire & Arran"        "Borders"                
##  [3] "Dumfries & Galloway"     "Fife"                   
##  [5] "Forth Valley"            "Grampian"               
##  [7] "Greater Glasgow & Clyde" "Highland"               
##  [9] "Lanarkshire"             "Lothian"                
## [11] "Orkney"                  "Shetland"               
## [13] "Tayside"                 "Western Isles"          
## [15] "Scotland"